﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data;

namespace ReklamaWPF.Grids
{
    /// <summary>
    /// Interaction logic for AdvertGrid.xaml
    /// </summary>
    public partial class AdvertGrid : UserControl
    {
        DataBase db = new DataBase("192.168.100.100\\SQLEXPRESS", "sa", "tornado", "PASystem");

        public AdvertGrid()
        {
            InitializeComponent();
            AdvertiseGridControl.View.ShowGroupPanel = false;
            datePicker1.DisplayDate = DateTime.Now;
            datePicker1.Text = DateTime.Now.ToShortDateString();
        }

        public void LoadData(DataTable tb)
        {
            AdvertiseGridControl.DataSource = tb;
        }

        public void LoadDataByDate(DateTime tm)
        {
            DataTable tb = db.getTable("select *,case BlockPos when 1 then 1 else case BlockPos when 2 then 2 else case BlockPos when 99 then 99 else 5 end  end  end AS 'PlaceInBlock',MONTH(Date) AS 'Month',DatePart(HOUR,Duration) * 3600 + DatePart(MINUTE, Duration) * 60 + DatePart(SECOND, Duration) AS 'ShortDuration', [Lietuviškas pavadinimas] AS 'Prog_Name' from AdvertisingData left join Advertisers adv on Advertiser_id=adv.id left join Campaigns cpg on Compaign_id=cpg.id left join Archyvas$ prg on Program_id=prg.id left join Clips on Clip_Id=Clips.id left join Blocks on [Time]>[Time_from] and [Time]<[Time_to] where Date='" + tm.ToShortDateString() + "' and (Archived is null or Archived=0)");
            AdvertiseGridControl.DataSource = tb;
        }

        private void datePicker1_SelectedDateChanged(object sender, SelectionChangedEventArgs e)
        {

            DataTable tb = db.getTable("select *,case BlockPos when 1 then 1 else case BlockPos when 2 then 2 else case BlockPos when 99 then 99 else 5 end  end  end AS 'PlaceInBlock',MONTH(Date) AS 'Month',DatePart(HOUR,Duration) * 3600 + DatePart(MINUTE, Duration) * 60 + DatePart(SECOND, Duration) AS 'ShortDuration', [Lietuviškas pavadinimas] AS 'Prog_Name' from AdvertisingData left join Advertisers adv on Advertiser_id=adv.id left join Campaigns cpg on Compaign_id=cpg.id left join Archyvas$ prg on Program_id=prg.id left join Clips on Clip_Id=Clips.id left join Blocks on [Time]>[Time_from] and [Time]<[Time_to] where Date='" + datePicker1.Text + "' and (Archived is null or Archived=0)");
            AdvertiseGridControl.DataSource = tb;
        }

        public void archiveRecord()
        {
            int selectedRowhandle = AdvertiseGridControl.View.GetSelectedRowHandles()[0];
            object id = AdvertiseGridControl.GetCellValue(selectedRowhandle, "id");
            db.sqlExecute("update AdvertisingData set Archived=1 where id=" + id.ToString());
            DataTable tb = db.getTable("select *,case BlockPos when 1 then 1 else case BlockPos when 2 then 2 else case BlockPos when 99 then 99 else 5 end  end  end AS 'PlaceInBlock',MONTH(Date) AS 'Month',DatePart(HOUR,Duration) * 3600 + DatePart(MINUTE, Duration) * 60 + DatePart(SECOND, Duration) AS 'ShortDuration', [Lietuviškas pavadinimas] AS 'Prog_Name' from AdvertisingData left join Advertisers adv on Advertiser_id=adv.id left join Campaigns cpg on Compaign_id=cpg.id left join Archyvas$ prg on Program_id=prg.id left join Clips on Clip_Id=Clips.id left join Blocks on [Time]>[Time_from] and [Time]<[Time_to] where Date='" + datePicker1.Text + "' and (Archived is null or Archived=0)");
            AdvertiseGridControl.DataSource = tb;
        }

        public void deleteRecord()
        {
            int selectedRowhandle = AdvertiseGridControl.View.GetSelectedRowHandles()[0];
            object id = AdvertiseGridControl.GetCellValue(selectedRowhandle, "id");
            db.sqlExecute("delete from AdvertisingData where id=" + id.ToString());
            DataTable tb = db.getTable("select *,case BlockPos when 1 then 1 else case BlockPos when 2 then 2 else case BlockPos when 99 then 99 else 5 end  end  end AS 'PlaceInBlock',MONTH(Date) AS 'Month',DatePart(HOUR,Duration) * 3600 + DatePart(MINUTE, Duration) * 60 + DatePart(SECOND, Duration) AS 'ShortDuration', [Lietuviškas pavadinimas] AS 'Prog_Name' from AdvertisingData left join Advertisers adv on Advertiser_id=adv.id left join Campaigns cpg on Compaign_id=cpg.id left join Archyvas$ prg on Program_id=prg.id left join Clips on Clip_Id=Clips.id left join Blocks on [Time]>[Time_from] and [Time]<[Time_to] where Date='" + datePicker1.Text + "' and (Archived is null or Archived=0)");
            AdvertiseGridControl.DataSource = tb;
        }

        public void editRecord(string searchwordAdv)
        {
            int selectedRowhandle = AdvertiseGridControl.View.GetSelectedRowHandles()[0];
            object id = AdvertiseGridControl.GetCellValue(selectedRowhandle, "id");

                AdvertisingAdd iw = new AdvertisingAdd();
                iw.red = true;
                Functionality.updateAdvIntoDB(iw, AdvertiseGridControl, searchwordAdv, id.ToString());
                iw.red = false;

                DataTable tb = db.getTable("select *,case BlockPos when 1 then 1 else case BlockPos when 2 then 2 else case BlockPos when 99 then 99 else 5 end  end  end AS 'PlaceInBlock',MONTH(Date) AS 'Month',DatePart(HOUR,Duration) * 3600 + DatePart(MINUTE, Duration) * 60 + DatePart(SECOND, Duration) AS 'ShortDuration', [Lietuviškas pavadinimas] AS 'Prog_Name' from AdvertisingData left join Advertisers adv on Advertiser_id=adv.id left join Campaigns cpg on Compaign_id=cpg.id left join Archyvas$ prg on Program_id=prg.id left join Clips on Clip_Id=Clips.id join Blocks on [Time]>[Time_from] and [Time]<[Time_to] where Date='" + datePicker1.Text + "' and (Archived is null or Archived=0)");
            AdvertiseGridControl.DataSource = tb;
        }

        public void restoreArchiveRecord()
        {
            int selectedRowhandle = AdvertiseGridControl.View.GetSelectedRowHandles()[0];
            object id = AdvertiseGridControl.GetCellValue(selectedRowhandle, "id");
            db.sqlExecute("update AdvertisingData set Archived=0 where id=" + id.ToString());
            DataTable tb = db.getTable("select *,case BlockPos when 1 then 1 else case BlockPos when 2 then 2 else case BlockPos when 99 then 99 else 5 end  end  end AS 'PlaceInBlock',MONTH(Date) AS 'Month',DatePart(HOUR,Duration) * 3600 + DatePart(MINUTE, Duration) * 60 + DatePart(SECOND, Duration) AS 'ShortDuration', [Lietuviškas pavadinimas] AS 'Prog_Name' from AdvertisingData left join Advertisers adv on Advertiser_id=adv.id left join Campaigns cpg on Compaign_id=cpg.id left join Archyvas$ prg on Program_id=prg.id left join Clips on Clip_Id=Clips.id left join Blocks on [Time]>[Time_from] and [Time]<[Time_to] where Date='" + datePicker1.Text + "' and Archived=1");
            AdvertiseGridControl.DataSource = tb;
        }

        public void deleteArchRecord()
        {
            int selectedRowhandle = AdvertiseGridControl.View.GetSelectedRowHandles()[0];
            object id = AdvertiseGridControl.GetCellValue(selectedRowhandle, "id");
            db.sqlExecute("delete from AdvertisingData where id=" + id.ToString());
            DataTable tb = db.getTable("select *,case BlockPos when 1 then 1 else case BlockPos when 2 then 2 else case BlockPos when 99 then 99 else 5 end  end  end AS 'PlaceInBlock',MONTH(Date) AS 'Month',DatePart(HOUR,Duration) * 3600 + DatePart(MINUTE, Duration) * 60 + DatePart(SECOND, Duration) AS 'ShortDuration', [Lietuviškas pavadinimas] AS 'Prog_Name' from AdvertisingData left join Advertisers adv on Advertiser_id=adv.id left join Campaigns cpg on Compaign_id=cpg.id left join Archyvas$ prg on Program_id=prg.id left join Clips on Clip_Id=Clips.id left join Blocks on [Time]>[Time_from] and [Time]<[Time_to] where Date='" + datePicker1.Text + "' and Archived=1");
            AdvertiseGridControl.DataSource = tb;
        }


        public void editArchivedRecord(string searchwordAdv)
        {
            int selectedRowhandle = AdvertiseGridControl.View.GetSelectedRowHandles()[0];
            object id = AdvertiseGridControl.GetCellValue(selectedRowhandle, "id");

            AdvertisingAdd iw = new AdvertisingAdd();
            iw.red = true;
            Functionality.updateAdvIntoDB(iw, AdvertiseGridControl, searchwordAdv, id.ToString());
            iw.red = false;

            DataTable tb = db.getTable("select *,case BlockPos when 1 then 1 else case BlockPos when 2 then 2 else case BlockPos when 99 then 99 else 5 end  end  end AS 'PlaceInBlock',MONTH(Date) AS 'Month',DatePart(HOUR,Duration) * 3600 + DatePart(MINUTE, Duration) * 60 + DatePart(SECOND, Duration) AS 'ShortDuration', [Lietuviškas pavadinimas] AS 'Prog_Name' from AdvertisingData left join Advertisers adv on Advertiser_id=adv.id left join Campaigns cpg on Compaign_id=cpg.id left join Archyvas$ prg on Program_id=prg.id left join Clips on Clip_Id=Clips.id join Blocks on [Time]>[Time_from] and [Time]<[Time_to] where Date='" + datePicker1.Text + "' and Archived=1");
            AdvertiseGridControl.DataSource = tb;
        }
    }
}
